ALTER PROCEDURE [dbo].[spe_grab_ucis_sfslbd] 
	-- Add the parameters for the stored procedure here

AS 
BEGIN
	declare @g_nf int
	declare @g_yf int
	declare @g_yhbh	varchar(20)
	declare @g_lysl int
	declare @g_sccbrq datetime
	declare @g_cbrq	datetime
	declare @g_ysl int
	declare @g_bdlv	decimal(9,4)
	declare @g_lxdh varchar(20)
	declare @g_sjhm varchar(30)
	declare @g_ysdz varchar(100)
	declare @g_gss varchar(50)
	declare @g_result varchar(4)
	declare @g_TelResult nchar(10)
	declare @g_sendTime datetime
	declare @g_id int
	declare @g_itemid bigint
	declare @g_deptcode varchar(10)			
	declare @plancount int,@datacount int,@totalcount int,@phonecount int
	declare @rec_guid varchar(36),@DEPTCODE varchar(10)
	declare @SENDYEARMONTH INT
	declare @BATCH INT
	declare @MESSAGETYPENAME VARCHAR(50)
	declare @MESSAGESTATUS INT
	declare @createtime DATETIME
	declare @MESSAGETYPECODE VARCHAR(50)
	declare @MESSAGECOUNT VARCHAR(50)
	declare @SMS_NAME VARCHAR(50)
	declare @MESSAGEINFOID VARCHAR(50)
	DECLARE @tguid VARCHAR(36)
	DECLARE @sname VARCHAR(255)	
	DECLARE @te_Starttime DATETIME
	Set @te_Starttime = GetDate()
	/*select @plancount=count(*) from ucis_smssendplan where type_id='A00013' and GETDATE()>=JHKSTIME and GETDATE()<=JHJSTIME and STATUS=0*/
	PRINT '-----查询UCIS计划------'
	SELECT @Plancount = Count(*) From OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo')
		WHERE MESSAGETYPECODE = 'A00013' AND MESSAGESTATUS = '1' AND SENDYEARMONTH = REPLACE(CONVERT(varchar(7),GETDATE(),120) , '-', '') and CREATETIME> CONVERT(varchar(100), GETDATE(), 23) 
		PRINT @Plancount
	if @Plancount>0
	begin
		SELECT top 1 @MESSAGEINFOID=MESSAGEINFOID,
								 @SENDYEARMONTH=SENDYEARMONTH,--年月
								 @BATCH=BATCH,--批次
								 @MESSAGETYPENAME=MESSAGETYPENAME,--类型（名称）
								 @DEPTCODE=DEPTCODE,--部门编号
								 @MESSAGECOUNT=MESSAGECOUNT,--短信总数
								 @MESSAGESTATUS=MESSAGESTATUS,--短信状态
								 @createtime=createtime,--时间
								 @MESSAGETYPECODE=MESSAGETYPECODE --类型(编号)
			From OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo')
			WHERE MESSAGETYPECODE = 'A00013' 
			AND MESSAGESTATUS = '1' 
			AND SENDYEARMONTH = REPLACE(CONVERT(varchar(7),GETDATE(),120) , '-', '') 
			ORDER BY createtime DESC

		 SELECT @plancount=COUNT(*) FROM Ucis_Smssendplan 
			WHERE TYPE_ID = @MESSAGETYPECODE 
				AND BATCH=@BATCH 
				AND DEPTCODE=@DEPTCODE 
				AND REPLACE(CONVERT(varchar(7),CTEATEDDATE,120) , '-', '')=@SENDYEARMONTH
				AND MESSAGEINFOID=@MESSAGEINFOID
		--PRINT '-----查询本地计划-----'
		--PRINT @plancount
		if @plancount<1

		BEGIN
		--PRINT '------------创建计划-------------'
		--小于1则说明本地计划表没有相匹配的计划任务，这时则需创建计划任务
			INSERT INTO Ucis_Smssendplan(rec_guid,TYPE_ID,BATCH,CTEATEDDATE,DEPTCODE,SMS_NAME,status,JHKSTIME,MESSAGEINFOID,SMS_JSSC,MESSAGECOUNT)
					SELECT top 1 NEWID(),MESSAGETYPECODE,BATCH,GETDATE(),m1.DEPTCODE,
					LEFT(SENDYEARMONTH,4)+'年'+SUBSTRING(SENDYEARMONTH,5,6)+'月'+c1.Department+m1.MESSAGETYPENAME + '批次('+CAST(BATCH as VARCHAR)+'-'+CAST(BATCHTIMES as VARCHAR) +')',0,createtime,MESSAGEINFOID,300,MESSAGECOUNT
					From OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo')m1
					LEFT JOIN ucis_deptcode c1 on c1.DEPTCODE=m1.DEPTCODE
					WHERE MESSAGETYPECODE = 'A00013' AND MESSAGESTATUS = '1' AND SENDYEARMONTH = REPLACE(CONVERT(varchar(7),GETDATE(),120) , '-', '') AND MESSAGEINFOID=@MESSAGEINFOID
					ORDER BY createtime DESC

		declare plan_A00013 cursor for select REC_GUID,DEPTCODE from ucis_smssendplan 
					where type_id='A00013' and STATUS=0 AND MESSAGEINFOID=@MESSAGEINFOID ORDER BY CTEATEDDATE DESC
		open plan_A00013 fetch next from plan_A00013 into @rec_guid,@DEPTCODE
		while @@fetch_status=0
		begin
			
			select @datacount=COUNT(*) FROM OPENQUERY(UCIS115,'SELECT * FROM ucis.ucis_sf_slbd where ghbtype is null ') 
				where yf=MONTH(GETDATE()) 
				and nf=YEAR(GETDATE()) 
				and DEPTCODE=@DEPTCODE
				AND BATCH=@BATCH 
			/*select @datacount=COUNT(*) from sw_ucis_slbd_test where GHBtype is null 
			and yf=MONTH(GETDATE()) and nf=YEAR(GETDATE()) and DEPTCODE=@DEPTCODE*/
			
			if @datacount>0
			begin
				insert into ucis_sms_monitoring(SMS_GUID,source_sum,type_id,grab_starttime,createdate)
				values(@rec_guid,@datacount,'A00013',GETDATE(),GETDATE())
				
				insert into UCIS_CJ_result(cj_guid,type,sourcedata,createdate) 
				values(@rec_guid,'A00013',@datacount,GETDATE())

				begin
					--写elite对应表
					begin 
					--调整了dblink查询语句sql方式，提高查询性能跟效率 Yf = Month(Getdate()) And Nf = Year(Getdate())
						insert into ucis_sf_slbd
									(id,nf,yf,yhbh,lysl,sccbrq,cbrq,ysl,bdlv,lxdh,sjhm,ysdz,gss,result,TelResult,sendTime,itemid,deptcode,SMS_GUID) 
						select id,nf,yf,yhbh,lysl,sccbrq,cbrq,ysl,bdlv,lxdh,sjhm,ysdz,gss,result,TelResult,sendTime,itemid,deptcode,@rec_guid 
						from OPENQUERY(UCIS115,'SELECT * FROM ucis.ucis_sf_slbd where ghbtype is null and yf=to_char(sysdate,''mm'') and nf=to_char(sysdate,''yyyy'')') 
						where 1=1
						and DEPTCODE=@DEPTCODE
						AND BATCH=@BATCH 
					end
					--更改状态
					begin
					--调整了dblink查询语句sql方式，提高查询性能跟效率 Yf = Month(Getdate()) And Nf = Year(Getdate())
						Update OPENQUERY (UCIS115,'SELECT * from ucis.ucis_sf_slbd where ghbtype is null and yf=to_char(sysdate,''mm'') and nf=to_char(sysdate,''yyyy'')') 
							set GHBtype = 1 
						where 1=1
							And Deptcode = @Deptcode
							AND BATCH=@BATCH 
					end
	
				end
				--计数
				begin 
					select @phonecount=COUNT(*) from ucis_sf_slbd where sms_guid=@rec_guid and LEN(sjhm)=11
					PRINT @phonecount
					update UCIS_CJ_result set phone_count=@phonecount where cj_guid=@rec_guid
					select @totalcount=count(*) from ucis_sf_slbd where  sms_guid=@rec_guid
					PRINT @totalcount
					update ucis_sms_monitoring set grab_sum=@totalcount,grab_endtime=GETDATE(),grab_status=1 where SMS_GUID=@rec_guid
					insert into ucis_sms_grab values(@rec_guid,'A00013',NULL,NULL)
				END
				/*begin 
					update ucis_smssendplan set status=1 where rec_guid=@rec_guid and status=0
				end*/
			end
		fetch next from plan_A00013 into @rec_guid,@DEPTCODE
		end
		close plan_A00013
		deallocate plan_A00013
	END
		--if @MESSAGESTATUS=1
				BEGIN 
					UPDATE TOP(1) OPENQUERY(UCIS115,'SELECT * FROM ucis.MessageInfo') SET MESSAGESTATUS = '2',modifytime = GETDATE()
					WHERE SENDYEARMONTH=@SENDYEARMONTH AND
								BATCH=@BATCH AND
								MESSAGETYPENAME=@MESSAGETYPENAME AND
								DEPTCODE=@DEPTCODE AND
								MESSAGECOUNT=@MESSAGECOUNT AND
								MESSAGESTATUS=@MESSAGESTATUS AND
								createtime=@createtime AND
								MESSAGETYPECODE=@MESSAGETYPECODE AND
								MESSAGEINFOID=@MESSAGEINFOID
				END
				BEGIN
					select top 1 @tguid = REC_GUID,@sname = sms_name from ucis_smssendplan 
							where MESSAGEINFOID = @MESSAGEINFOID ORDER BY CTEATEDDATE DESC
					INSERT INTO message_operation_his VALUES (@tguid,@sname,'job', GETDATE(), 'UCIS存在计划，进行计划抓取',NEWID());
				END
				BEGIN
					Insert Into Log_Proc_Dealtime
						(Log_Guid, Proc_Name, Run_No, Starttime, Endtime, Dealinfo, Dealtime, Remark)
					Values
						(Newid(), 'spe_grab_ucis_sfslbd', @tguid, @te_Starttime, Getdate(), '水量波动计划抓取及短信内容抓取', DateDiff(MS, @te_Starttime, GetDate()), 'spe_grab_ucis_sfslbd任务调用:水务催缴流程水量波动计划抓取及短信内容抓取')

				END
	end
end